﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
//using System.Web.Configuration;
//using System.Net.Http.Headers;
using System.Configuration;

namespace DPMQWindowsService.Src
{
    public class connMetalLDB
    {
        public static string constring()
        {
            string strcn = "";
            
            strcn = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();

            return strcn;
        }
        
        public static string constringPDCA()
        {
            string strcn = "";
            strcn = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();
            return strcn;

        }
        public static string constringCCDB()
        {
            string strcn = "";
            strcn = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();
            return strcn;

        }
        public static string constringLaser()
        {
            string strcn = "";
            strcn = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();
            return strcn;

        }
        public static SqlConnection returncon()
        {
            string sql = constring();
            SqlConnection con;
            try
            {
                con = new SqlConnection(sql);
            }
            catch
            {
                con = null;
                return con;
            }
            return con;
        }
        public static void conclose(SqlConnection con)
        {
            if (con.State.ToString() == "Open")
            {
                con.Close();
            }
        }
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(constring()))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(SQLString, connection);
                    cmd.CommandTimeout = 3000;
                    SqlDataAdapter command = new SqlDataAdapter(cmd);
                    command.Fill(ds);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行带参数的存储过程(并且有输出参数)
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="SN"></param>
        /// <returns>返回输出参数</returns>
        public static string QueryProcedure(string strSQL,string SN)
        {
            using (SqlConnection connection = new SqlConnection(constringPDCA()))
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, connection);
                    //设置cmd类型
                    cmd.CommandType = CommandType.StoredProcedure;
                    //设置超时
                    cmd.CommandTimeout = 3000;
                    // 创建参数
                    IDataParameter[] parameters = {
                    new SqlParameter("@Msg", SqlDbType.VarChar,50) ,
                    new SqlParameter("@SerialNumber", SqlDbType.VarChar,100) ,
                    };
                    // 设置参数类型
                    parameters[0].Direction = ParameterDirection.Output;  // 设置为输出参数
                    parameters[1].Value = SN;
                    // 添加参数
                    cmd.Parameters.Add(parameters[0]);
                    cmd.Parameters.Add(parameters[1]);
                    cmd.ExecuteNonQuery();
                    return parameters[0].Value.ToString();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    return "Fail："+ex.Message;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
               
            }
        }
        public static int Insert(string sql)
        {
            SqlConnection mycon = returncon();
            mycon.Open();
            SqlTransaction mytran = mycon.BeginTransaction();
            SqlCommand mycommand = new SqlCommand(sql, mycon);
            mycommand.Transaction = mytran;
            try
            {
                mycommand.ExecuteNonQuery();
                mytran.Commit();
                return 1;
            }
            catch(Exception ex)
            {
                LogWriter.Instance.writeError(ex.ToString());
                //LogHelp.WriteToServerCVTXT("InsertErr:" + DateTime.Now  );
                if(mytran!=null)
                {
                    mytran.Rollback();
                }
                if(mycon!=null)
                {
                    connMetalLDB.conclose(mycon);
                }               
                return 0;
            }
            finally
            {
                connMetalLDB.conclose(mycon);
                mytran.Dispose();
            }
         
        }
        public static int InsertCCDB(string sql)
        {
            using (SqlConnection connection = new SqlConnection(constringCCDB()))
            {
                connection.Open();
                SqlTransaction mytran = connection.BeginTransaction();
                SqlCommand mycommand = new SqlCommand(sql, connection);
                mycommand.Transaction = mytran;
                try
                {
                    mycommand.ExecuteNonQuery();
                    mytran.Commit();
                    return 1;
                }
                catch
                {
                    LogHelp.WriteToServerCVTXT("InsertErr:" + DateTime.Now);
                    mytran.Rollback();
                    connection.Close();
                    return 0;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }
        public static DataSet QueryLaser(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(constringLaser()))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(SQLString, connection);
                    cmd.CommandTimeout = 3000;
                    SqlDataAdapter command = new SqlDataAdapter(cmd);
                    command.Fill(ds);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
                return ds;
            }
        }
        public static int InsertLaser(string sql)
        {
            using (SqlConnection connection = new SqlConnection(constringLaser()))
            {
                connection.Open();
                SqlTransaction mytran = connection.BeginTransaction();
                SqlCommand mycommand = new SqlCommand(sql, connection);
                mycommand.Transaction = mytran;
                try
                {
                    mycommand.ExecuteNonQuery();
                    mytran.Commit();
                    return 1;
                }
                catch
                {
                    LogHelp.WriteToServerCVTXT("InsertErr:" + DateTime.Now);
                    mytran.Rollback();
                    connection.Close();
                    return 0;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }
    }
}